#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    this->setWindowState(Qt::WindowMaximized);
    ///链接数据库
    ///这里有个细节：db.addDatabase("QSQLITE")的写法是错误的
    db=QSqlDatabase::addDatabase("QSQLITE");
    ///踩过的坑+1，query要和db链接
    query=QSqlQuery(db);
    ///后面发布软件的时候要修改这个路径
    QString file="../DataBase/PurchaseMenagement.db";
    db.setDatabaseName(file);
    if(!db.open())
    {
        qInfo()<<"未能打开数据库";
    }
    ///加载数据库
    open_table();
    ///定时器的设置
    second=0;
    timer_0=new QTimer(this);
    timer_0->stop();
    timer_0->setInterval(1000);
    connect(timer_0,SIGNAL(timeout()),this,SLOT(on_timer_0_timeout()));

    time_1=new QTimer(this);
    time_1->stop();
    time_1->setInterval(3000);
    connect(time_1,SIGNAL(timeout()),this,SLOT(on_timer_1_timeout()));
    stop_select_flag=false;

    ///数据表设置
    ui->cmpTable->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ///按键失能
    ui->pushButton_2->setEnabled(false);
}

MainWindow::~MainWindow()
{
    db.close();
    delete ui;
}

///登陆按键按下
void MainWindow::on_pushButton_clicked()
{
    if(ui->email->text().isEmpty() || !ui->email->text().contains("@"))
    {
        ui->label_tips->setText("请留下您的邮箱");
    }
    else
    {
        query.prepare("select * from UserData where email=:E");
        query.bindValue(":E",ui->email->text());
        query.exec();
        if(query.next())
        {
            if(query.value("identity").toString()=="黑名单")
            {
                ui->label_tips->setText("抱歉请离开！");
            }
            else
            {
                auto data=query.value("photo").toByteArray();
                QPixmap pic;
                pic.loadFromData(data);
                ui->label_photo->setPixmap(pic.scaledToWidth(ui->label_photo->width()));

                ui->lineEdit_2->setText(ui->email->text());
                ui->label_tips->setText("欢迎您！"+query.value("identity").toString());
                ///设置数据信息
                //open_table();
                if(query.value("identity").toString()=="管理员")
                {
                    ui->tableView_6->setEditTriggers(QAbstractItemView::AllEditTriggers);
                    ui->stackedWidget_2->setCurrentIndex(0);
                }
                else
                {
                    ui->tableView_6->setEditTriggers(QAbstractItemView::NoEditTriggers);
                    ui->stackedWidget_2->setCurrentIndex(1);
                }
            }
        }
        else
        {
            ui->label_tips->setText("欢迎加入物料库！");
            query.prepare("insert into userdata (email,identity)values(?,'访客')");
            query.bindValue(0,ui->email->text());
            query.exec();
            tabEmail->select();
        }
    }
    second=3;
    timer_0->start();

}

///定时中断服务函数
void MainWindow::on_timer_0_timeout()
{
    if(second>0)
    {
        second--;
    }
    if(second==0)
    {
        timer_0->stop();
        if(ui->label_tips->text()=="请留下您的邮箱" || ui->label_tips->text()=="抱歉请离开！")
        {

        }
        else
        {
            ui->email->clear();
            ui->stackedWidget->setCurrentIndex(1);
        }
        ///设置用户的信息
        ui->label_tips->setText("");
    }
}
///刷新表格
void MainWindow::on_timer_1_timeout()
{
    stop_select_flag=false;
    time_1->stop();
}

///初始化数据模型
void MainWindow::open_table()
{
    tabMaterials=new QSqlRelationalTableModel(this,db);
    tabMaterials->setTable("Materials");
    tabMaterials->setSort(tabMaterials->fieldIndex("id"),Qt::AscendingOrder);
    tabMaterials->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabMaterials->select()))
    {
        qInfo()<<"error";
    }
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("id"),Qt::Horizontal,"编号");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("name"),Qt::Horizontal,"名称");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("finish"),Qt::Horizontal,"已完善");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("type_id"),Qt::Horizontal,"类型");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("number"),Qt::Horizontal,"物料量");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("model"),Qt::Horizontal,"封装");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("discrete"),Qt::Horizontal,"价格离散");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("quality"),Qt::Horizontal,"质量/%");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("lead_time"),Qt::Horizontal,"交期/h");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("Suppliers_id"),Qt::Horizontal,"供应商");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("buy_times"),Qt::Horizontal,"已购次数");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("buy_counts"),Qt::Horizontal,"已购数量");
    tabMaterials->setHeaderData(tabMaterials->fieldIndex("return"),Qt::Horizontal,"退货次数");
    //tabMaterials->setHeaderData(tabMaterials->fieldIndex(""),Qt::Horizontal,"");

    ///关系型数据表设置
    tabMaterials->setRelation(tabMaterials->fieldIndex("Suppliers_id"),
                              QSqlRelation("Suppliers","id","name"));

    tabMaterials->setRelation(tabMaterials->fieldIndex("type_id"),
                              QSqlRelation("Mat_type","id","name"));

    theSelection=new QItemSelectionModel(tabMaterials);
    ///connect
    connect(theSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanges(QModelIndex,QModelIndex)));

    ///与ui建立联系
    ui->tableView->setModel(tabMaterials);
    ui->tableView->setSelectionModel(theSelection);
    ///隐藏不需要显示的列
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("cost_0"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("cost_post"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("memo"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("http"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("buy_min"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("buy_max"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("count_dis_th"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("count_dis_num"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("count_exe_th"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("count_exe_num"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("pay_dis_th"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("pay_dis_num"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("pay_exe_th"),true);
    ui->tableView->setColumnHidden(tabMaterials->fieldIndex("pay_exe_num"),true);
    ///设置列宽
    ui->tableView->setColumnWidth(tabMaterials->fieldIndex("name"),200);
    ui->tableView->setColumnWidth(tabMaterials->fieldIndex("Suppliers_id"),200);
    ui->tableView->setColumnWidth(tabMaterials->fieldIndex("type_id"),200);
    ///启用关系型代理
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));


    ///数据映射
    dataMapper=new QDataWidgetMapper();
    dataMapper->setModel(tabMaterials);
    dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper->addMapping(ui->return_cnts,tabMaterials->fieldIndex("return"));
    dataMapper->addMapping(ui->material_id,tabMaterials->fieldIndex("id"));
    dataMapper->addMapping(ui->material_name,tabMaterials->fieldIndex("name"));
    dataMapper->addMapping(ui->material_model,tabMaterials->fieldIndex("model"));
    dataMapper->addMapping(ui->material_lead_time,tabMaterials->fieldIndex("lead_time"));
    dataMapper->addMapping(ui->comboBox_discrete,tabMaterials->fieldIndex("discrete"));
    dataMapper->addMapping(ui->material_quality,tabMaterials->fieldIndex("quality"));
    dataMapper->addMapping(ui->buy_min,tabMaterials->fieldIndex("buy_min"));
    dataMapper->addMapping(ui->buy_max,tabMaterials->fieldIndex("buy_max"));
    dataMapper->addMapping(ui->material_buy_times,tabMaterials->fieldIndex("buy_times"));
    dataMapper->addMapping(ui->material_buy_counts,tabMaterials->fieldIndex("buy_counts"));
    dataMapper->addMapping(ui->cost_post,tabMaterials->fieldIndex("cost_post"));
    dataMapper->addMapping(ui->cost_0,tabMaterials->fieldIndex("cost_0"));
    dataMapper->addMapping(ui->count_1,tabMaterials->fieldIndex("count_dis_th"));
    dataMapper->addMapping(ui->count_2,tabMaterials->fieldIndex("count_exe_th"));
    dataMapper->addMapping(ui->pay_1,tabMaterials->fieldIndex("pay_dis_th"));
    dataMapper->addMapping(ui->pay_2,tabMaterials->fieldIndex("pay_exe_th"));
    dataMapper->addMapping(ui->count_dis,tabMaterials->fieldIndex("count_dis_num"));
    dataMapper->addMapping(ui->count_exe,tabMaterials->fieldIndex("count_exe_num"));
    dataMapper->addMapping(ui->pay_dis,tabMaterials->fieldIndex("pay_dis_num"));
    dataMapper->addMapping(ui->pay_exe,tabMaterials->fieldIndex("pay_exe_num"));
    dataMapper->addMapping(ui->http,tabMaterials->fieldIndex("http"));
    dataMapper->addMapping(ui->comboBox_finish,tabMaterials->fieldIndex("finish"));
    dataMapper->addMapping(ui->memo,tabMaterials->fieldIndex("memo"));


    tabMaterials->select();
    dataMapper->toFirst();

    ///////////////////////////////////////////////////////////////////////////////////
    tabDiscrete=new QSqlTableModel(this,db);
    tabDiscrete->setTable("Discrete");
    tabDiscrete->setSort(tabDiscrete->fieldIndex("id"),Qt::AscendingOrder);
    tabDiscrete->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabDiscrete->select()))
    {
        qInfo()<<"error";
    }
    tabDiscrete->setHeaderData(tabDiscrete->fieldIndex("id"),Qt::Horizontal,"编号");
    tabDiscrete->setHeaderData(tabDiscrete->fieldIndex("num"),Qt::Horizontal,"数量");
    tabDiscrete->setHeaderData(tabDiscrete->fieldIndex("money"),Qt::Horizontal,"金额");
    //tabDiscrete->setHeaderData(tabDiscrete->fieldIndex(""),Qt::Horizontal,"");
    theSelection_2=new QItemSelectionModel(tabDiscrete);
    ///connect

    ///与ui建立联系
    ui->tableView_2->setModel(tabDiscrete);
    ui->tableView_2->setSelectionModel(theSelection_2);

    ///隐藏不需要显示的列
    ui->tableView_2->setColumnHidden(tabDiscrete->fieldIndex("id"),true);

    tabDiscrete->select();
    tabDiscrete->setFilter(QObject::tr("id= %1").arg(ui->material_id->text()));

    ///////////////////////////////////////////////////////////////////////////////////
    tabSuppliers=new QSqlTableModel(this,db);
    tabSuppliers->setTable("Suppliers");
    tabSuppliers->setSort(tabSuppliers->fieldIndex("id"),Qt::AscendingOrder);
    tabSuppliers->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabSuppliers->select()))
    {
        qInfo()<<"error";
    }
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("id"),Qt::Horizontal,"供应商编号");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("name"),Qt::Horizontal,"品牌");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("platform"),Qt::Horizontal,"平台");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("type"),Qt::Horizontal,"财务类型");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("ISO"),Qt::Horizontal,"ISO");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("legal_person"),Qt::Horizontal,"法人");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("position"),Qt::Horizontal,"发货地址");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("credit"),Qt::Horizontal,"信誉分");
    tabSuppliers->setHeaderData(tabSuppliers->fieldIndex("pay"),Qt::Horizontal,"已支出");
    //tabSupper->setHeaderData(tabSupper->fieldIndex(""),Qt::Horizontal,"");
    theSelection_3=new QItemSelectionModel(tabSuppliers);
    ///connect链接mapper
    connect(theSelection_3,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanges_3(QModelIndex,QModelIndex)));

    ///与ui建立联系
    ui->tableView_3->setModel(tabSuppliers);
    ui->tableView_3->setSelectionModel(theSelection_3);

    ///隐藏不需要显示的列
    ui->tableView_3->setColumnHidden(tabSuppliers->fieldIndex("external_assistance"),true);
    ui->tableView_3->setColumnHidden(tabSuppliers->fieldIndex("customers"),true);
    for(int i=0;i<7;i++)
        if(i==1)
            ui->tableView_3->setColumnWidth(i,200);
        else
            ui->tableView_3->setColumnWidth(i,150);

    ///mapper映射
    dataMapper_3=new QDataWidgetMapper();
    dataMapper_3->setModel(tabSuppliers);
    dataMapper_3->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper_3->addMapping(ui->Sup_id,tabSuppliers->fieldIndex("id"));
    dataMapper_3->addMapping(ui->Sup_name,tabSuppliers->fieldIndex("name"));
    dataMapper_3->addMapping(ui->Sup_type,tabSuppliers->fieldIndex("type"));
    dataMapper_3->addMapping(ui->Sup_position,tabSuppliers->fieldIndex("position"));
    dataMapper_3->addMapping(ui->Sup_ISO,tabSuppliers->fieldIndex("ISO"));
    dataMapper_3->addMapping(ui->Sup_leagal,tabSuppliers->fieldIndex("legal_person"));
    dataMapper_3->addMapping(ui->Sup_credit,tabSuppliers->fieldIndex("credit"));
    dataMapper_3->addMapping(ui->Sup_ex,tabSuppliers->fieldIndex("external_assistance"));
    dataMapper_3->addMapping(ui->Sup_customer,tabSuppliers->fieldIndex("customers"));
    dataMapper_3->addMapping(ui->Sup_platform,tabSuppliers->fieldIndex("platform"));

    dataMapper_3->toFirst();
    tabSuppliers->select();
    ///////////////////////////////////////////////////////////////////////////////////
    tabOrder=new QSqlRelationalTableModel(this,db);
    tabOrder->setTable("orders");
    tabOrder->setSort(tabOrder->fieldIndex("id"),Qt::AscendingOrder);
    tabOrder->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabOrder->select()))
    {
        qInfo()<<"error";
    }
    tabOrder->setHeaderData(tabOrder->fieldIndex("id"),Qt::Horizontal,"交易编号");
    tabOrder->setHeaderData(tabOrder->fieldIndex("num"),Qt::Horizontal,"交易数目");
    tabOrder->setHeaderData(tabOrder->fieldIndex("money"),Qt::Horizontal,"交易金额");
    tabOrder->setHeaderData(tabOrder->fieldIndex("state"),Qt::Horizontal,"订单状态");
    tabOrder->setHeaderData(tabOrder->fieldIndex("order_time"),Qt::Horizontal,"下单时间");
    tabOrder->setHeaderData(tabOrder->fieldIndex("recive_time"),Qt::Horizontal,"收货时间");
    tabOrder->setHeaderData(tabOrder->fieldIndex("material_id"),Qt::Horizontal,"物料编号");
    tabOrder->setHeaderData(tabOrder->fieldIndex("material_id_name"),Qt::Horizontal,"物料名称");
    tabOrder->setHeaderData(tabOrder->fieldIndex("material_cnts"),Qt::Horizontal,"物料数量");
    tabOrder->setHeaderData(tabOrder->fieldIndex("radio"),Qt::Horizontal,"良品率");

    theSelection_4=new QItemSelectionModel(tabOrder);
    ///connect
    connect(theSelection_4,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanges_4(QModelIndex,QModelIndex)));

    tabOrder->setRelation(tabOrder->fieldIndex("material_id_name"),QSqlRelation("materials","id","name"));

    ///与ui建立联系
    ui->tableView_4->setModel(tabOrder);
    ui->tableView_4->setSelectionModel(theSelection_4);
    ui->tableView_4->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->tableView_4->setColumnWidth(tabOrder->fieldIndex("material_id_name"),200);
    ui->tableView_4->setColumnWidth(tabOrder->fieldIndex("order_time"),230);
    ui->tableView_4->setColumnWidth(tabOrder->fieldIndex("recive_time"),230);

    ui->tableView_4->setItemDelegate(new QSqlRelationalDelegate(ui->tableView_4));

    ///隐藏不需要显示的列
    ui->tableView_4->setColumnHidden(tabOrder->fieldIndex("bad_cnts"),true);

    ///数据映射
    dataMapper_4=new QDataWidgetMapper();
    dataMapper_4->setModel(tabOrder);
    dataMapper_4->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper_4->addMapping(ui->order_id,tabOrder->fieldIndex("id"));
    dataMapper_4->addMapping(ui->order_id_2,tabOrder->fieldIndex("material_id"));
    dataMapper_4->addMapping(ui->order_id_name,tabOrder->fieldIndex("material_id_name"));
    dataMapper_4->addMapping(ui->order_money,tabOrder->fieldIndex("money"));
    dataMapper_4->addMapping(ui->order_state,tabOrder->fieldIndex("state"));
    dataMapper_4->addMapping(ui->order_time,tabOrder->fieldIndex("order_time"));
    dataMapper_4->addMapping(ui->recive_time,tabOrder->fieldIndex("recive_time"));
    dataMapper_4->addMapping(ui->order_num,tabOrder->fieldIndex("num"));
    dataMapper_4->addMapping(ui->order_material_num,tabOrder->fieldIndex("material_cnts"));
    dataMapper_4->addMapping(ui->good_radio,tabOrder->fieldIndex("radio"));
    dataMapper_4->addMapping(ui->bad_cnts,tabOrder->fieldIndex("bad_cnts"));

    dataMapper_4->toFirst();
    tabOrder->select();
    ///////////////////////////////////////////////////////////////////////////////////
    tabType=new QSqlTableModel(this,db);
    tabType->setTable("Mat_type");
    tabType->setSort(tabType->fieldIndex("id"),Qt::AscendingOrder);
    tabType->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabType->select()))
    {
        qInfo()<<"error";
    }
    tabType->setHeaderData(tabType->fieldIndex("id"),Qt::Horizontal,"类型编号");
    tabType->setHeaderData(tabType->fieldIndex("name"),Qt::Horizontal,"类型名称");
    tabType->setHeaderData(tabType->fieldIndex("mat_data_cnts"),Qt::Horizontal,"物料数据量");
    tabType->setHeaderData(tabType->fieldIndex("order_mat_cnts"),Qt::Horizontal,"订单物料量");
    tabType->setHeaderData(tabType->fieldIndex("money"),Qt::Horizontal,"类型采购额");

    theSelection_5=new QItemSelectionModel(tabType);
    ///connect
    connect(theSelection_5,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanges_5(QModelIndex,QModelIndex)));
    ///与ui建立联系
    ui->tableView_5->setModel(tabType);
    ui->tableView_5->setSelectionModel(theSelection_5);

    ui->tableView_5->setColumnWidth(tabType->fieldIndex("name"),400);
    ui->tableView_5->setColumnWidth(tabType->fieldIndex("mat_data_cnts"),200);
    ui->tableView_5->setColumnWidth(tabType->fieldIndex("order_mat_cnts"),200);
    ui->tableView_5->setColumnWidth(tabType->fieldIndex("money"),200);
    ///数据映射
    dataMapper_5=new QDataWidgetMapper();
    dataMapper_5->setModel(tabType);
    dataMapper_5->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper_5->addMapping(ui->mat_type_id,tabType->fieldIndex("id"));
    dataMapper_5->addMapping(ui->mat_type_name,tabType->fieldIndex("name"));
    dataMapper_5->addMapping(ui->mat_data_cnts,tabType->fieldIndex("mat_data_cnts"));
    dataMapper_5->addMapping(ui->order_mat_cnts,tabType->fieldIndex("order_mat_cnts"));
    dataMapper_5->addMapping(ui->type_money,tabType->fieldIndex("money"));

    tabType->select();
    dataMapper_5->toFirst();
    ///////////////////////////////////////////////////////////////////////////////////
    tabEmail=new QSqlTableModel(this,db);
    tabEmail->setTable("UserData");
    tabEmail->setSort(tabEmail->fieldIndex("email"),Qt::AscendingOrder);
    tabEmail->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabEmail->select()))
    {
        qInfo()<<"error";
    }
    tabEmail->setHeaderData(tabEmail->fieldIndex("email"),Qt::Horizontal,"用户邮箱");
    tabEmail->setHeaderData(tabEmail->fieldIndex("identity"),Qt::Horizontal,"用户身份");


    theSelection_6=new QItemSelectionModel(tabEmail);
    ///connect
    connect(theSelection_6,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanges_6(QModelIndex,QModelIndex)));
    ///与ui建立联系
    ui->tableView_6->setModel(tabEmail);
    ui->tableView_6->setSelectionModel(theSelection_6);

    ui->tableView_6->setColumnHidden(tabEmail->fieldIndex("photo"),true);

    ui->tableView_6->setColumnWidth(tabEmail->fieldIndex("email"),400);
    ui->tableView_6->setColumnWidth(tabEmail->fieldIndex("identity"),200);

    ///数据映射
    dataMapper_6=new QDataWidgetMapper();
    dataMapper_6->setModel(tabEmail);
    dataMapper_6->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper_6->addMapping(ui->lineEdit_6,tabEmail->fieldIndex("email"));
    dataMapper_6->addMapping(ui->comboBox_2,tabEmail->fieldIndex("identity"));

    tabEmail->select();
    dataMapper_6->toFirst();
    ///////////////////////////////////////////////////////////////////////////////////
    tabTrigger=new QSqlTableModel(this,db);
    tabTrigger->setTable("MyTrigger");
    tabTrigger->setSort(tabTrigger->fieldIndex("id"),Qt::AscendingOrder);
    tabTrigger->setEditStrategy(QSqlTableModel::OnFieldChange);
    if(!(tabTrigger->select()))
    {
        qInfo()<<"error";
    }
    ///与ui建立联系
    ui->tableView_7->setModel(tabTrigger);
    ///数据映射
    dataMapper_7=new QDataWidgetMapper();
    dataMapper_7->setModel(tabTrigger);
    dataMapper_7->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);

    dataMapper_7->addMapping(ui->mytriggerdata,tabTrigger->fieldIndex("flag"));

    tabTrigger->select();
    dataMapper_7->toFirst();

}
///排序函数,它可以用来刷新数据表
void MainWindow::table_sort(QSqlTableModel *model,int index=0)
{
    model->setSort(index,Qt::AscendingOrder);
    model->select();
}

///计算价格的函数，相当重要！
double MainWindow::calculate_cost(int id, int num)
{
    query.prepare("select cost_post,cost_0,"                                //0 1
                  "buy_min,buy_max,number,"                                 //2 3 4
                  "count_dis_th,count_dis_num,count_exe_th,count_exe_num,"  //5 6 7 8
                  "pay_dis_th,pay_dis_num,pay_exe_th,pay_exe_num,"          //9 10 11 12
                  "discrete"                                                //13
                  " from Materials where id=?");
    query.bindValue(0,id);
    query.exec();


    if(query.next())
    {
        if(num<query.value(2).toInt() || num>query.value(3).toInt())    return 0;
        if(query.value(13).toString()=="是") //离散计算
        {
            query.prepare("select money from discrete where id=? and num=?");
            query.bindValue(0,id);
            query.bindValue(1,num);
            query.exec();
            if(query.next())
            {
                return query.value(0).toDouble();
            }
            else
            {
                return 0;
            }
        }
        else
        {
            double money=0,dis_count=0;
            double num_D=num;
            int temp_count=0,times=0;
            double temp_money;
            money+=query.value(1).toDouble()*num_D;
            if((money>=query.value(9).toDouble()&& query.value(10).toDouble()<1)
                    || (money>=query.value(11).toDouble()&& query.value(12).toDouble()>0))
            {
                temp_money=0;
                if(money>=query.value(9).toDouble()&& query.value(10).toDouble()<1)
                {
                    times=money/query.value(9).toDouble();
                    temp_count=(money-times*query.value(9).toDouble())/query.value(1).toDouble();
                    temp_money=temp_count*query.value(1).toDouble();
                    dis_count+=times*(money-temp_money)*(1-query.value(10).toDouble());
                }
                if(money>=query.value(11).toDouble()&& query.value(12).toDouble()>0)
                {
                    times=money/query.value(11).toDouble();
                    temp_count=(money-times*query.value(11).toDouble())/query.value(1).toDouble();
                    temp_money=temp_count*query.value(1).toDouble();
                    dis_count+=times*query.value(12).toDouble();
                }
                ///拼多多的优惠卷算法
                if(temp_count>=query.value(5).toInt())
                {
                    dis_count+=temp_money*(1-query.value(6).toDouble());
                }
                if(temp_count>=query.value(7).toInt())
                {
                    dis_count+=query.value(8).toDouble();
                }
            }
            else
            {
                if(num>=query.value(5).toInt())
                {
                    dis_count+=money*(1-query.value(6).toDouble());
                }
                if(num>=query.value(7).toInt())
                {
                    dis_count+=query.value(8).toDouble();
                }
            }
            money+=query.value(0).toDouble();
            money-=dis_count;

            return money;
        }
    }
    else
    {
        return 0;
    }
}

///最小编号生成器
int MainWindow::min_id(QString id, QString table,QString state)
{
    qint32 last_data=0,now_data;

    query.exec(QObject::tr("select %1 from %2 %3").arg(id).arg(table).arg(state));
    if(query.next())
    {
        last_data=query.value(0).toInt();
    }
    if(last_data!=1)    ///一个记录都没有,或者“1”被删了
    {
        return 1;
    }
    while(query.next())
    {
        now_data=query.value(0).toInt();
        if(now_data!=last_data+1)   break;
        last_data=now_data;
    }
    return (last_data+1);
}

///设置最优表的一行数据
void MainWindow::input_item(int row, int num, int id, QString name, double money)
{
    QTableWidgetItem *item;
    item=ui->best_choice->item(row,1);
    item->setText(QString::number(num));
    item=ui->best_choice->item(row,2);
    item->setText(QString::number(id));
    item=ui->best_choice->item(row,3);
    item->setText(name);
    item=ui->best_choice->item(row,4);
    item->setText(QString::number(money,'f',2));
}

///获取目标行
int MainWindow::get_row(QSqlTableModel *model, int column, QString data)
{
    for(int i=0;i<model->rowCount();i++)
    {
        QSqlRecord rec=model->record(i);
        if(rec.value(column).toString()==data)
            return i;
    }
    return 0;
}




///新增一个物料
void MainWindow::on_pushButton_6_clicked()
{
    ///如果为空则返回
    if(ui->material_get_name->text().isEmpty())
        return;
    ///生成物料编号的最小编号
    int row=min_id("id","Materials","");
    ///1.使用sql语言的方法
    query.prepare("insert into Materials (id,name)values(?,?)");
    query.bindValue(0,row);
    query.bindValue(1,ui->material_get_name->text());
    query.exec();
    ///刷新数据表
    tabMaterials->select();
    theSelection->setCurrentIndex(tabMaterials->index(row-1,0),QItemSelectionModel::Select);
    ///清空输入框
    ui->material_get_name->clear();
}

///选中行变化后的函数
void MainWindow::on_currentRowChanges(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ///行切换时的控制
    dataMapper->setCurrentIndex(current.row());
    tabDiscrete->setFilter(QObject::tr("id= %1").arg(ui->material_id->text()));
}
///选中行变化后的函数
void MainWindow::on_currentRowChanges_3(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ///行切换时的控制
    dataMapper_3->setCurrentIndex(current.row());
}
///选中行变化后的函数
void MainWindow::on_currentRowChanges_4(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ///行切换时的控制
    dataMapper_4->setCurrentIndex(current.row());
}
///选中行变化后的函数
void MainWindow::on_currentRowChanges_5(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ///行切换时的控制
    dataMapper_5->setCurrentIndex(current.row());
}
///选中行变化后的函数
void MainWindow::on_currentRowChanges_6(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    ///行切换时的控制
    dataMapper_6->setCurrentIndex(current.row());

    QSqlRecord rec=tabEmail->record(current.row());
    if(rec.isNull("photo"))
    {
        ui->label_photo_2->clear();
    }
    else
    {
        auto data=rec.value("photo").toByteArray();
        QPixmap pic;
        pic.loadFromData(data);
        ui->label_photo_2->setPixmap(pic.scaledToWidth(ui->label_photo_2->width()));
    }


}

///删除物料
void MainWindow::on_pushButton_7_clicked()
{
    query.prepare("delete from Materials where id=?");
    query.bindValue(0,ui->material_id->text().toInt());
    query.exec();
    table_sort(tabMaterials);
    int row=min_id("id","Materials","");
    qInfo()<<row;
    theSelection->setCurrentIndex(tabMaterials->index(row-2,0),QItemSelectionModel::Select);
}

///主页的搜索框
void MainWindow::on_lineEdit_textChanged(const QString &arg1)
{
    if(arg1.isEmpty())
    {
        tabMaterials->setFilter("");
    }
    else
    {
        QString str=QObject::tr("(Materials.id like '%%1%')"
                                "OR (Materials.name like '%%1%')"
                                "OR (Materials.model like '%%1%')"
                                "OR (Materials.quality like '%%1%')"
                                "OR (Materials.lead_time like '%%1%')"
                                "OR (Materials.buy_counts like '%%1%')"
                                ).arg(arg1);
        tabMaterials->setFilter(str);
    }
    tabMaterials->select();
}

///比较的搜索按钮
void MainWindow::on_pushButton_9_clicked()
{
    QString arg1=ui->lineEdit_8->text();
    if(arg1=="")   return;
    query.exec(QObject::tr("select id,name,model,number,finish from Materials where name like '%%1%'").arg(arg1));
    int curRow;
    QTableWidgetItem *item;
    while(query.next())
    {
        if(ui->checkBox->isChecked() && query.value("finish").toString()=="否")
            continue;

        curRow=ui->cmpTable->rowCount();
        ///如果表格为空的话，这个行数是0
        if(curRow==0)
        {
            ui->cmpTable->insertRow(curRow);
            ///直接进行添加操作
            for(int i=0;i<4;i++)
            {
                item=new QTableWidgetItem();
                item->setText(query.value(i).toString());
                ui->cmpTable->setItem(curRow,i,item);
            }
        }
        else
        {
            bool flag=false;
            ///查重
            for(int i=0;i<curRow;i++)
            {
                if(ui->cmpTable->item(i,0)->text().toInt()==query.value(0).toInt())
                {
                    flag=true;
                    break;
                }
            }
            ///查重的结果判断
            if(flag)
            {
                continue;
            }
            else
            {
                ui->cmpTable->insertRow(curRow);
                for(int i=0;i<4;i++)
                {
                    item=new QTableWidgetItem();
                    item->setText(query.value(i).toString());
                    ui->cmpTable->setItem(curRow,i,item);
                }
            }
        }
    }
}

///清空所有的比价清单
void MainWindow::on_pushButton_5_clicked()
{
    int row=ui->cmpTable->rowCount();
    for(int i=0;i<row;i++)
        ui->cmpTable->removeRow(0);
}

///清除所选行
void MainWindow::on_pushButton_8_clicked()
{
    int row=ui->spinBox_del->value();
    if(row>ui->cmpTable->rowCount())    return;
    row--;
    ui->cmpTable->removeRow(row);
}

///大小机制
void MainWindow::on_cnt_begin_valueChanged(int arg1)
{
    if(ui->cnt_end->value()<arg1)   ui->cnt_end->setValue(arg1);
}
void MainWindow::on_cnt_end_valueChanged(int arg1)
{
    if(ui->cnt_begin->value()>arg1)   ui->cnt_begin->setValue(arg1);
}


///开始生成图形
void MainWindow::on_pushButton_3_clicked()
{
    ///表格处理
    int row=ui->best_choice->rowCount();
    QTableWidgetItem *item;
    for(int i=0;i<row;i++)
        ui->best_choice->removeRow(0);
    int row_best=ui->cnt_end->value()-ui->cnt_begin->value()+1;
    ui->best_choice->setRowCount(row_best);
    for(int i=0;i<row_best;i++)
    {
        for(int j=0;j<5;j++)
        {
            item=new QTableWidgetItem();
            if(j==0)    item->setText(QString::number(i+ui->cnt_begin->value()));
            ui->best_choice->setItem(i,j,item);
        }
    }

    ///图表处理
    int row_cmp=ui->cmpTable->rowCount();
    int id,num;
    double money,exist_money;
    QString str;
    QChart *chart=ui->num_pay->chart();
    chart->removeAllSeries();
    foreach (auto ax, chart->axes()) {
        chart->removeAxis(ax);
    }
    QScatterSeries *series;

    for(int i=0;i<row_cmp;i++)
    {
        id=ui->cmpTable->item(i,0)->text().toInt();
        query.prepare("select number,name from Materials where id=?");
        query.bindValue(0,id);
        query.exec();
        if(query.next())
        {
            str=query.value("name").toString();
            ///获取一单的数量
            num=query.value("number").toInt();

            series=new QScatterSeries();
            series->setBorderColor(Qt::black);
            series->setColor(Qt::black);
            series->setMarkerSize(6);

            for(int j=ui->cnt_begin->value();j<=ui->cnt_end->value();j++)
            {
                ///如果符合则进行计算
                if(j%num==0)
                {
                    money=calculate_cost(id,j/num);
                    ///检测一下钱是否正确
                    if(!(money>0)) continue;
                    ///曲线上添加新的点
                    series->append(j,money);

                    ///如果是空则填入表中
                    ///或者比原有的小
                    ///或者发生梯度下降
                    ///就填入表格
                    for(int k=j-ui->cnt_begin->value();k>=0;k--)
                    {
                        exist_money=ui->best_choice->item(k,4)->text().toDouble();
                        if(money<exist_money || exist_money<=0)
                        {
                            input_item(k,j/num,id,str,money);
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            }
            ///遍历数量完毕，将生成的点数据放入图表中
            chart->addSeries(series);
        }
    }
    /// 生成最小比价曲线,并放入图表
    series=new QScatterSeries();
    series->setBorderColor(Qt::green);
    series->setColor(Qt::green);
    series->setMarkerSize(8);
    for(int i=0;i<row_best;i++)
    {
        if(!ui->best_choice->item(i,1)->text().isEmpty())
        {
            num=ui->best_choice->item(i,0)->text().toInt();
            money=ui->best_choice->item(i,4)->text().toDouble();
            series->append(num,money);
        }
    }
    chart->addSeries(series);
    /// 更新图形
    chart->createDefaultAxes();
    chart->axes(Qt::Horizontal).first()->setRange(ui->cnt_begin->value(),ui->cnt_end->value());
    chart->legend()->setVisible(false);

}

///搜索价格
void MainWindow::on_input_id_textChanged(const QString &arg1)
{
    ui->radioButton->setChecked(true);
    int id=arg1.toInt();
    bool flag;
    if(arg1.isEmpty() || ui->radioButton_2->isChecked())  flag=false;
    query.prepare("select id,name from Materials where id=?");
    query.bindValue(0,id);
    query.exec();
    if(query.next())
    {
        flag=true;
        ui->buy_name->setText(query.value("name").toString());
        ui->pay_money->setText(QString::number(calculate_cost(id,ui->buy_num->value()),'f',2));
    }
    else
    {
        ui->pay_money->setText("0");
        ui->buy_name->setText("");
    }
    ui->pushButton_2->setEnabled(flag);
}

///改变数量
void MainWindow::on_buy_num_valueChanged(int arg1)
{
    int id=ui->input_id->text().toInt();
    query.prepare("select id from Materials where id=?");
    query.bindValue(0,id);
    query.exec();
    if(query.next())
    {
        ui->pay_money->setText(QString::number(calculate_cost(id,arg1),'f',2));
    }
    else
    {
        ui->pay_money->setText("0");
    }
}

///离散价格
void MainWindow::on_comboBox_discrete_currentTextChanged(const QString &arg1)
{
    if(arg1=="是")
    {
        ui->stackedDiscrete->setCurrentIndex(1);
    }
    else
    {
        ui->stackedDiscrete->setCurrentIndex(0);
    }
}

///新增离散价格记录
void MainWindow::on_pushButton_10_clicked()
{
    ///1.使用sql语言的方法
    int row=min_id("num","discrete",QObject::tr("where id=%1").arg(ui->material_id->text().toInt()));
    QSqlQuery temp_query;
    temp_query.prepare("insert into Discrete (id,num)values(?,?)");
    temp_query.bindValue(0,ui->material_id->text().toInt());
    temp_query.bindValue(1,row);
    temp_query.exec();
    ///刷新数据表
    table_sort(tabDiscrete,1);
    theSelection_2->setCurrentIndex(tabDiscrete->index(row-1,2),QItemSelectionModel::Select);
}

///生成最小供应商编号
///新增供应商
void MainWindow::on_pushButton_14_clicked()
{
    if(ui->get_sup_name->text().isEmpty())  return;
    int row=min_id("id","Suppliers","");
    query.prepare("insert into Suppliers (id,name)values(?,?)");
    query.bindValue(0,row);
    query.bindValue(1,ui->get_sup_name->text());
    query.exec();
    table_sort(tabSuppliers);
    theSelection_3->setCurrentIndex(tabSuppliers->index(row-1,0),QItemSelectionModel::Select);
}

///刷新表格（？刷新了，但没完全刷新）
void MainWindow::on_tabWidget_currentChanged(int index)
{
    push_up();
    if(index==0)
    {
        open_table();
    }
    if(index==2)
    {
        sup_pay();
    }
    if(index==3)
    {

    }
    if(index==4)
    {
        type_count();
    }
    if(index==5)
    {
        create_table();
    }
}

///将光标移动到指定的行上（很好的函数）
void MainWindow::on_pushButton_12_clicked()
{
    auto id=ui->material_id->text();
    query.prepare("select Materials.Suppliers_id from Materials where Materials.id=?");
    query.bindValue(0,id);
    query.exec();
    if(query.next())
    {
        auto temp=query.value(0).toString();
        qInfo()<<temp;
        ui->tabWidget->setCurrentIndex(2);
        theSelection_3->setCurrentIndex(tabSuppliers->index(get_row(tabSuppliers,0,temp),0),QItemSelectionModel::Select);
    }
}

///排序功能
void MainWindow::on_comboBox_currentIndexChanged(int index)
{
    Q_UNUSED(index);
    int temp=0;
    for(int i=0;i<tabMaterials->columnCount();i++)
    {
        if(tabMaterials->headerData(i,Qt::Horizontal)==ui->comboBox->currentText())
        {
            temp=i;
            break;
        }
    }

    if(ui->radioAscend->isChecked())
    {
        tabMaterials->setSort(temp,Qt::AscendingOrder);
    }
    else
    {
        tabMaterials->setSort(temp,Qt::DescendingOrder);
    }
    tabMaterials->select();
}
void MainWindow::on_radioAscend_clicked()
{
    int temp=0;
    for(int i=0;i<tabMaterials->columnCount();i++)
    {
        if(tabMaterials->headerData(i,Qt::Horizontal)==ui->comboBox->currentText())
        {
            temp=i;
            break;
        }
    }
    tabMaterials->setSort(temp,Qt::AscendingOrder);
    tabMaterials->select();
}
void MainWindow::on_radioDescend_clicked()
{
    int temp=0;
    for(int i=0;i<tabMaterials->columnCount();i++)
    {
        if(tabMaterials->headerData(i,Qt::Horizontal)==ui->comboBox->currentText())
        {
            temp=i;
            break;
        }
    }
    tabMaterials->setSort(temp,Qt::DescendingOrder);
    tabMaterials->select();
}
///最后的选择
void MainWindow::on_radioButton_clicked()
{
    int id=ui->input_id->text().toInt();
    bool flag=false;
    if(ui->input_id->text().isEmpty() || ui->radioButton_2->isChecked())  flag=false;
    query.prepare("select id,name from Materials where id=?");
    query.bindValue(0,id);
    query.exec();
    if(query.next())
    {
        flag=true;
    }
    ui->pushButton_2->setEnabled(flag);
}
void MainWindow::on_radioButton_2_clicked()
{
    ui->pushButton_2->setEnabled(false);
    query.prepare("update materials set finish='否' where id=?");
    query.bindValue(0,ui->input_id->text().toInt());
    query.exec();
}

///生成订单
void MainWindow::on_pushButton_2_clicked()
{
    int id=min_id("id","orders","");
    int number;
    QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间
    QString str = dateTime.toString("yyyy-MM-dd hh:mm:ss");//格式化时间

    query.prepare("select Materials.number,Materials.name from Materials where Materials.id=?");
    query.bindValue(0,ui->input_id->text());
    query.exec();
    if(query.next())
    {
        number=query.value(0).toInt();
    }
    else
    {
        return;
    }

    qInfo()<<"开始添加";
    query.prepare("insert into orders (id,material_id,num,material_cnts,money,order_time,material_id_name)values(?,?,?,?,?,?,?)");
    query.bindValue(0,id);
    query.bindValue(1,ui->input_id->text());
    query.bindValue(2,ui->buy_num->value());
    query.bindValue(3,number*ui->buy_num->value());
    query.bindValue(4,ui->pay_money->text());
    query.bindValue(5,str);
    query.bindValue(6,ui->input_id->text());
    query.exec();
    tabOrder->select();

    query.prepare("select buy_times,buy_counts from materials where id=?");
    query.bindValue(0,ui->input_id->text());
    query.exec();
    if(query.next())
    {
        int buy_times=query.value("buy_times").toInt();
        buy_times++;
        int buy_counts=query.value("buy_counts").toInt()+number*ui->buy_num->value();
        ///数量更改完毕，准备更新
        query.prepare("update materials set buy_times=?,buy_counts=? where id=?");
        query.bindValue(0,buy_times);
        query.bindValue(1,buy_counts);
        query.bindValue(2,ui->input_id->text());
        query.exec();
    }

    ui->tabWidget->setCurrentIndex(3);
    ui->input_id->setText("");
    theSelection_4->setCurrentIndex(tabOrder->index(get_row(tabOrder,0,QString::number(id)),0),QItemSelectionModel::Select);

}

///交易记录的搜索框
void MainWindow::on_lineEdit_5_textChanged(const QString &arg1)
{
    if(arg1.isEmpty())
    {
        tabOrder->setFilter("");
    }
    else
    {
        QString str=QObject::tr("(orders.id like '%%1%')"
                                "OR (orders.material_id like '%%1%')"
                                "OR (orders.state like '%%1%')"
                                "OR (orders.num like '%%1%')"
                                "OR (orders.material_cnts like '%%1%')"
                                "OR (orders.order_time like '%%1%')"
                                "OR (orders.recive_time like '%%1%')"
                                "OR (orders.radio like '%%1%')"
                                "OR (orders.money like '%%1%')").arg(arg1);
        tabOrder->setFilter(str);
    }
}

///收货
void MainWindow::on_pushButton_16_clicked()
{
    if(ui->order_state->text()=="待收货")
    {
        QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间
        QDateTime ordertime;
        QString recive_time = dateTime.toString("yyyy-MM-dd hh:mm:ss");//格式化时间
        QString order_time;
        int material_id;
        //int material_cnts;
        qint64 diffs;//时间间隔
        query.prepare("update orders set state='已收货',recive_time=? where id=?");
        query.bindValue(0,recive_time);
        query.bindValue(1,ui->order_id->text());
        query.exec();

        tabOrder->select();
        dataMapper_4->setCurrentIndex(ui->order_id->text().toInt()-1);
        ///更新已购数量、交期等信息
        query.prepare("select material_id,order_time,material_cnts from orders where id=?");
        query.bindValue(0,ui->order_id->text());
        query.exec();
        if(query.next())
        {
            material_id=query.value("material_id").toInt();
            //material_cnts=query.value("material_cnts").toInt();
            order_time=query.value("order_time").toString();
            ///这里有很大的坑，后面要接上"yyyy-MM-dd HH:mm:ss"这个字符串才能生效
            ordertime=QDateTime::fromString(order_time,"yyyy-MM-dd HH:mm:ss");
            diffs=ordertime.secsTo(dateTime)/3600;

            query.prepare("select buy_times,lead_time from materials where id=?");
            query.bindValue(0,material_id);
            query.exec();
            if(query.next())
            {
                int buy_times=query.value("buy_times").toInt()-1;
                int lead_time_sum=query.value("lead_time").toInt()*buy_times+diffs;
                buy_times++;
                ///数量更改完毕，准备更新
                query.prepare("update materials set buy_times=?,buy_counts=?,lead_time=? where id=?");
                query.bindValue(0,buy_times);
                query.bindValue(1,lead_time_sum/buy_times);
                query.bindValue(2,material_id);
                query.exec();
            }
        }
    }
}

///退货
void MainWindow::on_pushButton_17_clicked()
{
    if(ui->order_state->text()!="已退货")
    {
        int material_cnts,material_id;
        query.prepare("select material_id,material_cnts from orders where id=?");
        query.bindValue(0,ui->order_id->text());
        query.exec();
        if(query.next())
        {
            material_id=query.value("material_id").toInt();
            material_cnts=query.value("material_cnts").toInt();

            query.prepare("select buy_counts,return from materials where id=?");
            query.bindValue(0,material_id);
            query.exec();
            if(query.next())
            {
                int buy_counts=query.value("buy_counts").toInt()-material_cnts;
                int return_cnts=query.value("return").toInt()+1;
                ///数量更改完毕，准备更新
                query.prepare("update materials set buy_counts=?,return=? where id=?");
                query.bindValue(0,buy_counts);
                query.bindValue(1,return_cnts);
                query.bindValue(2,material_id);
                query.exec();
            }
        }

        query.prepare("update orders set state='已收货',state=?,material_cnts=0 where id=?");
        query.bindValue(0,"已退货");
        query.bindValue(1,ui->order_id->text());
        query.exec();

        tabOrder->select();

        theSelection_4->setCurrentIndex(tabOrder->index(get_row(tabOrder,0,QString::number(min_id("id","orders","")-1)),0),QItemSelectionModel::Select);
    }

}

///新增类型
void MainWindow::on_pushButton_18_clicked()
{
    if(ui->get_mat_type_name->text().isEmpty())  return;
    ///生成物料编号的最小编号
    int row=min_id("id","Mat_type","");
    ///1.使用sql语言的方法
    query.prepare("insert into Mat_type (id,name)values(?,?)");
    query.bindValue(0,row);
    query.bindValue(1,ui->get_mat_type_name->text());
    query.exec();
    ///刷新数据表
    tabType->select();
    theSelection_5->setCurrentIndex(tabType->index(row-1,0),QItemSelectionModel::Select);
}

///删除类型
void MainWindow::on_pushButton_19_clicked()
{
    query.prepare("delete from Mat_type where id=?");
    query.bindValue(0,ui->mat_type_id->text().toInt());
    query.exec();
    table_sort(tabType);
    int row=min_id("id","Mat_type","");
    theSelection_5->setCurrentIndex(tabType->index(row-2,0),QItemSelectionModel::Select);
}

///退出登录
void MainWindow::on_pushButton_20_clicked()
{
    ui->stackedWidget->setCurrentIndex(0);
    ui->tabWidget->setCurrentIndex(0);
}

///供应商排序功能
void MainWindow::on_comboBox_4_currentTextChanged(const QString &arg1)
{
    int temp=0;
    for(int i=0;i<tabSuppliers->columnCount();i++)
    {
        if(tabSuppliers->headerData(i,Qt::Horizontal)==arg1)
        {
            temp=i;
            break;
        }
    }

    if(ui->radioButton_5->isChecked())
    {
        tabSuppliers->setSort(temp,Qt::AscendingOrder);
    }
    else
    {
        tabSuppliers->setSort(temp,Qt::DescendingOrder);
    }
    tabSuppliers->select();
}
void MainWindow::on_radioButton_5_clicked()
{
    int temp=0;
    for(int i=0;i<tabSuppliers->columnCount();i++)
    {
        if(tabSuppliers->headerData(i,Qt::Horizontal)==ui->comboBox_4->currentText())
        {
            temp=i;
            break;
        }
    }
    tabSuppliers->setSort(temp,Qt::AscendingOrder);
    tabSuppliers->select();
}
void MainWindow::on_radioButton_6_clicked()
{
    int temp=0;
    for(int i=0;i<tabSuppliers->columnCount();i++)
    {
        if(tabSuppliers->headerData(i,Qt::Horizontal)==ui->comboBox_4->currentText())
        {
            temp=i;
            break;
        }
    }
    tabSuppliers->setSort(temp,Qt::DescendingOrder);
    tabSuppliers->select();
}

///订单表格排序
void MainWindow::on_comboBox_5_currentTextChanged(const QString &arg1)
{
    int temp=0;
    for(int i=0;i<tabOrder->columnCount();i++)
    {
        if(tabOrder->headerData(i,Qt::Horizontal)==arg1)
        {
            temp=i;
            break;
        }
    }

    if(ui->radioButton_7->isChecked())
    {
        tabOrder->setSort(temp,Qt::AscendingOrder);
    }
    else
    {
        tabOrder->setSort(temp,Qt::DescendingOrder);
    }
    tabOrder->select();
}
void MainWindow::on_radioButton_7_clicked()
{
    int temp=0;
    for(int i=0;i<tabOrder->columnCount();i++)
    {
        if(tabOrder->headerData(i,Qt::Horizontal)==ui->comboBox_5->currentText())
        {
            temp=i;
            break;
        }
    }
    tabOrder->setSort(temp,Qt::AscendingOrder);
    tabOrder->select();
}
void MainWindow::on_radioButton_8_clicked()
{
    int temp=0;
    for(int i=0;i<tabOrder->columnCount();i++)
    {
        if(tabOrder->headerData(i,Qt::Horizontal)==ui->comboBox_5->currentText())
        {
            temp=i;
            break;
        }
    }
    tabOrder->setSort(temp,Qt::DescendingOrder);
    tabOrder->select();
}

///统计数量的函数
void MainWindow::type_count()
{
    QStringList str_list;
    query.exec("select id from mat_type");
    while(query.next())
    {
        str_list<<query.value("id").toString();
    }
    QStringList::const_iterator it;
    for(it=str_list.begin();it!=str_list.end();++it)
    {
        int count_0=0;
        query.prepare("select id from materials where type_id=?");
        query.bindValue(0,*it);
        query.exec();
        while(query.next())
        {
            count_0++;
        }
        query.prepare("select orders.material_cnts,orders.money from orders,materials,mat_type where materials.type_id=mat_type.id and orders.material_id=materials.id and mat_type.id=? and orders.state!='已退货'");
        query.bindValue(0,*it);
        query.exec();
        int count=0;
        double money=0;
        while(query.next())
        {
            count+=query.value(0).toInt();
            money+=query.value(1).toDouble();
        }
        ///更新数据
        query.prepare("update mat_type set mat_data_cnts=?,order_mat_cnts=?,money=? where id=?");
        query.bindValue(0,count_0);
        query.bindValue(1,count);
        query.bindValue(2,money);
        query.bindValue(3,*it);
        query.exec();
    }
    tabType->select();
}

///计算供应商的支出
void MainWindow::sup_pay()
{
    QStringList strList;

    query.exec("select id from Suppliers");
    while(query.next())
    {
        strList<<query.value("id").toString();
    }
    double money;
    QStringList::const_iterator it;
    for(it=strList.begin();it!=strList.end();++it)
    {
        money=0;
        query.prepare("select orders.money from Suppliers,orders,materials where (orders.state!='已退货') and (orders.material_id=materials.id) and (materials.Suppliers_id=Suppliers.id) and (Suppliers.id=?)");
        query.bindValue(0,*it);
        query.exec();
        while(query.next())
        {
            money+=query.value("orders.money").toDouble();
        }
        query.prepare("update Suppliers set pay=? where id=?");
        query.bindValue(0,money);
        query.bindValue(1,*it);
        query.exec();
        tabSuppliers->select();
    }

}

///图表刷新函数
void MainWindow::create_table()
{
    type_count();
    sup_pay();

    QChart *chart;

    chart=ui->graphicsView_1->chart();
    chart->removeAllSeries();

    int count=0;
    QPieSeries *series=new QPieSeries();
    QPieSlice *slice;
    series->setHoleSize(0.2);
    query.exec("select name,order_mat_cnts from mat_type");
    while(query.next())
    {
        if(query.value("order_mat_cnts").toInt()==0)   continue;
        series->append(query.value(0).toString(),query.value("order_mat_cnts").toFloat());
        count++;
        if(count>15)    break;
    }
    for(int i=0;i<count;i++)
    {
        slice=series->slices().at(i);
        slice->setLabel(slice->label()+QString::asprintf("有%.0f个物料,占比%.1f%%",slice->value(),slice->percentage()*100));
        connect(slice,SIGNAL(hovered(bool)),this,SLOT(on_PieHighlight(bool)));
    }
    series->setLabelsVisible(true);
    chart->addSeries(series);
    chart->setTitle("已购物料类型");
    chart->setTitleFont(QFont("微软雅黑",23));
    chart->legend()->setAlignment(Qt::AlignRight);
    ///////////////////////////////////////////////////////////////////////////////////////////////

    chart=ui->graphicsView_2->chart();
    chart->removeAllSeries();

    count=0;
    series=new QPieSeries();
    series->setHoleSize(0.2);
    query.exec("select name,mat_data_cnts from mat_type");
    while(query.next())
    {
        if(query.value("mat_data_cnts").toInt()==0)   continue;
        series->append(query.value(0).toString(),query.value("mat_data_cnts").toFloat());
        count++;
        if(count>15)    break;
    }
    for(int i=0;i<count;i++)
    {
        slice=series->slices().at(i);
        slice->setLabel(slice->label()+QString::asprintf("有%.0f个物料,占比%.1f%%",slice->value(),slice->percentage()*100));
        connect(slice,SIGNAL(hovered(bool)),this,SLOT(on_PieHighlight(bool)));
    }
    series->setLabelsVisible(true);
    chart->addSeries(series);
    chart->setTitle("已收录物料数据类型统计");
    chart->setTitleFont(QFont("微软雅黑",23));
    chart->legend()->setAlignment(Qt::AlignRight);
    ///////////////////////////////////////////////////////////////////////////////////////////////

    chart=ui->graphicsView_3->chart();
    chart->removeAllSeries();

    count=0;
    series=new QPieSeries();
    series->setHoleSize(0.2);
    query.exec("select name,pay from Suppliers");
    while(query.next())
    {
        if(query.value("pay").toInt()==0)   continue;
        series->append(query.value("name").toString(),query.value("pay").toFloat());
        count++;
        if(count>15)    break;
    }
    for(int i=0;i<count;i++)
    {
        slice=series->slices().at(i);
        slice->setLabel(slice->label()+QString::asprintf("支出%.2f元,占比%.1f%%",slice->value(),slice->percentage()*100));
        connect(slice,SIGNAL(hovered(bool)),this,SLOT(on_PieHighlight(bool)));
    }
    series->setLabelsVisible(true);
    chart->addSeries(series);
    chart->setTitle("供应商采购支出");
    chart->setTitleFont(QFont("微软雅黑",23));
    chart->legend()->setAlignment(Qt::AlignRight);
    ///////////////////////////////////////////////////////////////////////////////////////////////

    chart=ui->graphicsView_4->chart();
    chart->removeAllSeries();

    count=0;
    series=new QPieSeries();
    series->setHoleSize(0.2);
    query.exec("select name,money from mat_type");
    while(query.next())
    {
        if(query.value("money").toInt()==0)   continue;
        series->append(query.value("name").toString(),query.value("money").toFloat());
        count++;
        if(count>15)    break;
    }
    for(int i=0;i<count;i++)
    {
        slice=series->slices().at(i);
        slice->setLabel(slice->label()+QString::asprintf("支出%.2f元,占比%.1f%%",slice->value(),slice->percentage()*100));
        connect(slice,SIGNAL(hovered(bool)),this,SLOT(on_PieHighlight(bool)));
    }
    series->setLabelsVisible(true);
    chart->addSeries(series);
    chart->setTitle("类型采购额");
    chart->setTitleFont(QFont("微软雅黑",23));
    chart->legend()->setAlignment(Qt::AlignRight);
}
///提交代码
void MainWindow::push_up()
{
    tabMaterials->select();
    tabOrder->select();
    tabDiscrete->select();
    tabSuppliers->select();
    tabType->select();
    tabEmail->select();
}


///显示高亮
void MainWindow::on_PieHighlight(bool show)
{
    QPieSlice *slice=(QPieSlice *) sender();
    slice->setExploded(show);
}

///访问网页
void MainWindow::on_go2web_clicked()
{
    query.prepare("select http from Materials where id=?");
    query.bindValue(0,ui->input_id->text());
    query.exec();
    if(query.next())
    {
        theSelection->setCurrentIndex(tabMaterials->index(get_row(tabMaterials,0,ui->input_id->text()),0),QItemSelectionModel::Select);
        QClipboard *clip = QApplication::clipboard();
        clip->setText(ui->http->toPlainText());
        QDesktopServices::openUrl(QUrl((ui->http->toPlainText())));
    }
}

///搜索框
void MainWindow::on_lineEdit_3_textChanged(const QString &arg1)
{
    if(arg1.isEmpty())
    {
        tabType->setFilter("");
    }
    else
    {
        QString str=QObject::tr("(id like '%%1%')"
                                "OR (name like '%%1%')"
                                "OR (mat_data_cnts like '%%1%')"
                                "OR (order_mat_cnts like '%%1%')"
                                "OR (money like '%%1%')"
                                ).arg(arg1);
        tabType->setFilter(str);
    }
    tabType->select();
}

///打开并上传图片
void MainWindow::on_pushButton_4_clicked()
{
    QString afile=QFileDialog::getOpenFileName(this,"选择图片","","照片(*.jpg)");
    if(afile.isEmpty()) return;
    QByteArray data;
    QFile *file=new QFile(afile);
    file->open(QIODevice::ReadOnly);
    data=file->readAll();
    file->close();

    query.prepare("update userdata set photo=? where email=?");
    query.bindValue(0,data);
    query.bindValue(1,ui->lineEdit_2->text());
    query.exec();

    QPixmap pic;
    pic.load(afile);
    ui->label_photo->setPixmap(pic.scaledToWidth(ui->label_photo->width()));



}
///窗口背景设置以及大小调整
void MainWindow::setBackground() {
   /*
       const QString filePath = QLatin1String(":/demo01/x64/Debug/bgPic.png");
    QPixmap pixmap(filePath);
    QPalette palette = this->palette();
    palette.setBrush(backgroundRole(), QBrush(pixmap.scaled(this->size())));  // pixmap.scaled(this->size()) 这里需要注意，它决定了背景是否可拉伸。
    setPalette(palette);
   */
    QPalette PAllbackground = ui->frame_13->palette();
    QImage ImgAllbackground(":/new/prefix1/C:/Users/YYG18/Pictures/Screenshots/image.jpg");
    QImage fitimgpic = ImgAllbackground.scaled(ui->frame_13->width(), ui->frame_13->height(), Qt::IgnoreAspectRatio);
    PAllbackground.setBrush(QPalette::Window, QBrush(fitimgpic));
    ui->frame_13->setPalette(PAllbackground);
}

void MainWindow::resizeEvent(QResizeEvent* event) {
    Q_UNUSED(event);
    this->setBackground();
}

///更新商单的良品率信息
void MainWindow::on_bad_cnts_valueChanged(int arg1)
{
    query.prepare("select material_cnts from orders where id=?");
    query.bindValue(0,ui->order_id->text());
    query.exec();
    query.next();
    double radio=1-arg1/query.value(0).toDouble();
    if(radio>1) radio=1;
    if(radio<0) radio=0;
    query.prepare("update orders set bad_cnts=?,radio=? where id=?");
    query.bindValue(0,arg1);
    query.bindValue(1,radio);
    query.bindValue(2,ui->order_id->text());
    query.exec();
}

///发生变化后执行更新
void MainWindow::on_mytriggerdata_textChanged(const QString &arg1)
{
    if(arg1=="1")
    {
        type_count();
        sup_pay();
        qInfo()<<"触发器工作生效";
    }
    ui->mytriggerdata->setText("0");
}

///移除供应商
void MainWindow::on_pushButton_15_clicked()
{
    query.prepare("delete from Suppliers where id=?");
    query.bindValue(0,ui->Sup_id->text());
    query.exec();
    table_sort(tabSuppliers);
}

